# coding=utf-8
import os
import openpyxl
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
from config.all_paths import get_root_path, ai_report
from utils.time_tool.time_control import Getdate


class ExcelUtil(object):

    def __init__(self, filename, sheetname=None):
        self.filename = filename
        self.sheetname = sheetname

    def read_excel(self):
        """
        读取表格数据
        :return:
        """
        wb = load_workbook(self.filename)
        if self.sheetname is None:
            ws = wb.active  # active默认读取第一个表单
        else:
            ws = wb[self.sheetname]  # 读取指定表单

        testcase_list = []  # 所有数据信息
        header_list = []  # 表头信息
        for row in range(1, ws.max_row + 1):
            one_row_dict = {}  # 每一行数据信息
            for column in range(1, ws.max_column + 1):
                one_cell_value = ws.cell(row, column).value  # cell方法，获取单元格，返回Cell对象
                if row == 1:
                    header_list.append(one_cell_value)
                else:
                    key = header_list[column - 1]
                    one_row_dict[key] = one_cell_value
            # if row != 1 and one_row_dict['level'] == 1:
            if row != 1 and one_row_dict.get("is_run") != 1:
                testcase_list.append(one_row_dict)

        return testcase_list

    def write_excel1(self, data, path):
        """
        写操作
        :param row: 指定在某一行写
        :param column: 指定在某一列写
        :param data: 待写入的数据
        :return:
        """
        # 将数据写入到excel中，不能与读取操作公用一个Workbook对象
        # 如果使用同一个Workbook对象，只能将最后一次写入成功，会出现意想不到的结果
        wb = load_workbook(self.filename)
        if self.sheetname is None:
            ws = wb.active
        else:
            ws = wb[self.sheetname]
        for key, value in data.items():
            if len(value[0]) > 3000:
                str = value[0][:3000]
                ws.cell(key, 19, str)
                ws.cell(key, 20, value[1])
            else:
                ws.cell(key, 19, value[0])
                ws.cell(key, 20, value[1])

        # 对exel文件修改之后，要保存，一定要将excel文件关闭
        wb.save(path)

    def write_excel(self, data, path):
        wb = load_workbook(self.filename)
        if self.sheetname is None:
            ws = wb.active
        else:
            ws = wb[self.sheetname]

        for key, value in data.items():
            if len(value[0]) > 3000:
                str_value = value[0][:3000]
            else:
                str_value = value[0]
            ws.cell(row=key, column=19, value=str_value)
            ws.cell(row=key, column=20, value=value[1])
            if value[1] == "fail":
                for col in range(1, ws.max_column + 1):
                    cell = ws.cell(row=key, column=col)
                    cell.font = Font(color="FF0000")

        wb.save(path)


def writeToEXCEL(data):
    # 创建一个新的Workbook对象
    wb = Workbook()

    # 选择活动工作表
    ws = wb.active

    # 写入表头
    headers = list(data[0].keys())
    for col_num, header in enumerate(headers, 1):
        ws.cell(row=1, column=col_num, value=header)

    # 写入数据
    for row_num, row_data in enumerate(data, 2):
        for col_num, value in enumerate(row_data.values(), 1):
            ws.cell(row=row_num, column=col_num, value=value)

    # 保存Excel文件
    # wb.save("output.xlsx")
    wb.save(os.path.join(get_root_path(), 'data', "output.xlsx"))

def write_data_to_excel(data, output_file):

    historyData = data['history']
    del data['history']

    # 创建一个新的 Excel 工作簿和工作表
    workbook = openpyxl.Workbook()
    sheet = workbook.active

    # 设置表头
    headers = ["日期", "新增用户数", "新增宝宝数", "进AI小梧聊天数", "点击查看报告数", "主动提问数"]
    sheet.append(headers)

    # 设置表头样式（加粗、居中）
    for cell in sheet[1]:
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center', vertical='center')

    # 定义日期和数据的对应关系
    date_mapping = {
        "yesterday": Getdate().getYesterday(),
        "beforeYesterday": Getdate().getDayBeforeYesterday(),
        "3DayAgo": Getdate().getThreeDaysAgo(),
        "4DayAgo": Getdate().getFourDaysAgo(),
        "5DayAgo": Getdate().getFiveDaysAgo(),
        "6DayAgo": Getdate().getSixDaysAgo(),
        "7DayAgo": Getdate().getSevenDaysAgo()
    }

    # 遍历数据并写入表格
    for key, value in data.items():
        date = date_mapping.get(key, key)
        row_data = [
            date,
            value["accountNum"],
            value["childrenNum"],
            value["chatBabyNum"],
            value["cardUserNum"],
            value["askQuestionBabyNum"]
        ]
        sheet.append(row_data)

        # 设置当前行的单元格居中对齐
        for cell in sheet[sheet.max_row]:
            cell.alignment = Alignment(horizontal='center', vertical='center')

        # 插入历史数据行
    sheet.insert_rows(idx=10, amount=2)  # 在第10行插入两行
    sheet.cell(row=10, column=1).value = "历史进入AI页面次数≥2的人数"
    sheet.cell(row=10, column=2).value = historyData["repeatCenterUserNum"]
    sheet.cell(row=11, column=1).value = "历史产生会话≥2的人数"
    sheet.cell(row=11, column=2).value = historyData["repeatConversationUserNum"]

    # 设置历史数据行的样式
    for row in range(10, 12):
        for col in range(1, sheet.max_column + 1):
            cell = sheet.cell(row=row, column=col)
            cell.alignment = Alignment(horizontal='center', vertical='center')

    # 设置列宽
    for col in sheet.columns:
        max_length = 0
        column = col[0].column_letter  # 获取列的字母标识
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = (max_length + 20)
        sheet.column_dimensions[column].width = adjusted_width

    # 保存工作簿
    workbook.save(output_file)

if __name__ == '__main__':
    # dict1 = ExcelUtil(r"C:\Users\Mr.Yan\Downloads\医生注册及资料上传情况 (1).xlsx").read_excel()
    # print(dict1)
    # listall = []
    # for dict in dict1:
    #     new_dict = {
    #         "dietitianPhoneList": [str(dict["绑定医助登录账号"])],
    #         "doctorPhoneList": [str(dict["手机号"])],
    #     }
    #     listall.append(new_dict)
    #
    # # print(listall)
    # import json
    # print(json.dumps(listall))
    # print(len(listall))

    # butonglist = []
    # for listall_1 in listall:
    #     print(str(listall_1["手机号"])[-4:],str(listall_1["手机号匹配结果"])[-4:])
    #     # if listall_1["手机号"] is None or listall_1["手机号匹配结果"] is None or str(listall_1["手机号"])[-4:] != str(listall_1["手机号匹配结果"])[-4:]:
    #     if str(listall_1["手机号"])[-4:] != str(listall_1["手机号匹配结果"])[-4:]:
    #         butonglist.append(listall_1)
    #
    # print(butonglist)
    # dict1 = ExcelUtil(r"C:\Users\18017\Downloads\AI打标.xlsx").read_excel()
    # print(dict1)
    AI_XIAOWU = {
        'yesterday': {'accountNum': 6, 'childrenNum': 25, 'chatBabyNum': 0, 'cardUserNum': 0, 'askQuestionBabyNum': 1},
        'beforeYesterday': {'accountNum': 0, 'childrenNum': 0, 'chatBabyNum': 0, 'cardUserNum': 0,
                            'askQuestionBabyNum': 0},
        '3DayAgo': {'accountNum': 0, 'childrenNum': 0, 'chatBabyNum': 0, 'cardUserNum': 0, 'askQuestionBabyNum': 0},
        '4DayAgo': {'accountNum': 7, 'childrenNum': 12, 'chatBabyNum': 2, 'cardUserNum': 1, 'askQuestionBabyNum': 3},
        '5DayAgo': {'accountNum': 5, 'childrenNum': 14, 'chatBabyNum': 7, 'cardUserNum': 1, 'askQuestionBabyNum': 4},
        '6DayAgo': {'accountNum': 4, 'childrenNum': 19, 'chatBabyNum': 14, 'cardUserNum': 0, 'askQuestionBabyNum': 6},
        '7DayAgo': {'accountNum': 15, 'childrenNum': 18, 'chatBabyNum': 8, 'cardUserNum': 0, 'askQuestionBabyNum': 13},
        'history': {'repeatCenterUserNum': 71, 'repeatConversationUserNum': 68}}

    AI_XIAOWU1 = {
        'yesterday': {'accountNum': 6, 'childrenNum': 25, 'chatBabyNum': 0, 'cardUserNum': 0, 'askQuestionBabyNum': 1},
        'beforeYesterday': {'accountNum': 0, 'childrenNum': 0, 'chatBabyNum': 0, 'cardUserNum': 0,
                            'askQuestionBabyNum': 0},
        '3DayAgo': {'accountNum': 0, 'childrenNum': 0, 'chatBabyNum': 0, 'cardUserNum': 0, 'askQuestionBabyNum': 0},
        '4DayAgo': {'accountNum': 7, 'childrenNum': 12, 'chatBabyNum': 2, 'cardUserNum': 1, 'askQuestionBabyNum': 3},
        '5DayAgo': {'accountNum': 5, 'childrenNum': 14, 'chatBabyNum': 7, 'cardUserNum': 1, 'askQuestionBabyNum': 4},
        '6DayAgo': {'accountNum': 4, 'childrenNum': 19, 'chatBabyNum': 14, 'cardUserNum': 0, 'askQuestionBabyNum': 6},
        '7DayAgo': {'accountNum': 15, 'childrenNum': 18, 'chatBabyNum': 8, 'cardUserNum': 0, 'askQuestionBabyNum': 13}}

    write_data_to_excel(AI_XIAOWU, ai_report)


